package com.tgy.dao.user.userDaoImpl;

import com.mysql.cj.util.StringUtils;
import com.tgy.dao.BaseDao;
import com.tgy.dao.user.UserDao;
import com.tgy.entity.SmbmsUser;
import org.junit.Test;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/***
 * @ClassName: userDaoImpl
 * @Description:
 * @Auther: 送一一块二向箔
 * @Date: 22:20 2021/12/9
 * @version : V1.0
 */
public class UserDaoImpl implements UserDao {



    @Override
    public SmbmsUser getLoginUser(String userCode) throws SQLException {
        SmbmsUser user = new SmbmsUser();
        user.setUserCode(userCode);
        StringBuffer stringBuffer = new StringBuffer();
        stringBuffer.append("select * from `smbms_user` where userCode ='");
        stringBuffer.append(userCode);
        stringBuffer.append("'");
        Connection connection = BaseDao.getC();
        ResultSet rs = BaseDao.execute(1,connection , stringBuffer.toString(), null);
        if (rs.next()) {
            user.setId( rs.getInt("id"));
            user.setUserName(rs.getString("userName"));
            user.setUserPassword(rs.getString("userPassword"));
            user.setGender(rs.getInt("gender"));
            user.setBirthday(rs.getDate("birthday"));
            user.setPhone(rs.getString("phone"));
            user.setAddress(rs.getString("address"));
            user.setUserRole( rs.getInt("userRole"));
            user.setCreatedBy( rs.getInt("createdBy"));
            user.setCreationDate(rs.getTimestamp("creationDate"));
            user.setModifyBy(rs.getInt("modifyBy"));
            user.setModifyDate(rs.getTimestamp("modifyDate"));
        }
        BaseDao.closeResource(rs,null,connection);
        return user;
    }

    @Override
    public int changePassword(String userCode, String newPassword) throws SQLException {
        StringBuffer stringBuffer = new StringBuffer();
        stringBuffer.append("update `smbms_user` set userPassword = ?  where userCode = ?");
        String[] values ={newPassword,userCode};
        Connection connection = BaseDao.getC();
        int execute = BaseDao.execute(connection, stringBuffer.toString(), values);
        BaseDao.closeResource(null,null,connection);
        return execute;

    }

    @Override
    public int getNum(String name, int roleCode) {
        StringBuffer stringBuffer = new StringBuffer();
        int count = 0;
        stringBuffer.append("SELECT COUNT( u.`id` ) as count FROM `smbms_user` AS u LEFT JOIN `smbms_role` AS r ON r.`id` = u.`userRole` " +
                "WHERE u.`userRole` IS not NULL ");
        ArrayList<Object> objects = new ArrayList<>();
        if (!StringUtils.isNullOrEmpty(name)) {
            stringBuffer.append("and u.`userName` like ?");
            objects.add("%"+name+"%");
        }
        if(roleCode>0){
            stringBuffer.append("and u.`userRole` = ?");
            objects.add(roleCode);
        }
        //list转数组
        Object[] objects1 = objects.toArray();


        try {
            Connection connection = BaseDao.getC();
            ResultSet execute = BaseDao.execute(1, connection, stringBuffer.toString(), objects1);
            if(execute.next()){
                count = execute.getInt("count");
            }
            BaseDao.closeResource(null,null,connection);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return count;
    }

    @Override
    public List<SmbmsUser> getUserList(String name, int roleCode, int currentPageNo, int pageSize) throws Exception {
        StringBuffer stringBuffer = new StringBuffer();
        List<SmbmsUser> userList = new ArrayList<SmbmsUser>();
        stringBuffer.append("SELECT  u.*,r.roleName as userRoleName FROM `smbms_user` AS u LEFT JOIN `smbms_role` AS r ON r.`id` = u.`userRole` " +
                "WHERE u.`userRole` IS not NULL ");
        ArrayList<Object> objects = new ArrayList<>();
        if (!StringUtils.isNullOrEmpty(name)) {
            stringBuffer.append("and u.`userName` like ?");
            objects.add("%"+name+"%");
        }
        if(roleCode>0){
            stringBuffer.append("and u.`userRole` = ?");
            objects.add(roleCode);
        }

        //在数据库中，分页使用 limit startIndex，pagesize；总数
        //当前页（当前页-1）*页面大小
        //0，  5      1    0       01234
        //5，  5      2    5       26789
        //10， 5      3    10
        stringBuffer.append(" order by creationDate DESC limit ?,?");
        currentPageNo = (currentPageNo - 1) * pageSize;
        objects.add(currentPageNo);
        objects.add(pageSize);
        //list转数组
        Object[] objects1 = objects.toArray();
        Connection connection = BaseDao.getC();
        ResultSet rs = BaseDao.execute(1, connection, stringBuffer.toString(), objects1);
        while (rs.next()) {
            SmbmsUser _user = new SmbmsUser();
            _user.setId(rs.getInt("id"));
            _user.setUserCode(rs.getString("userCode"));
            _user.setUserName(rs.getString("userName"));
            _user.setGender(rs.getInt("gender"));
            _user.setBirthday(rs.getDate("birthday"));
            _user.setPhone(rs.getString("phone"));
            _user.setUserRole(rs.getInt("userRole"));
            _user.setUserRoleName(rs.getString("userRoleName"));
            userList.add(_user);
        }
        return  userList;
    }

    @Override
    public boolean userAdd(SmbmsUser user){
        int execute = 0;
        boolean flag = false;
        Connection c = null;

        String sql ="INSERT INTO `smbms`.`smbms_user` (`userCode`, `userName`, `userPassword`, `gender`, " +
                "`birthday`, `phone`, `address`, `userRole`, `createdBy`, `creationDate`) " +
                "ALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, SYSDATE());";

        Object[] params = {user.getUserCode(), user.getUserName(), user.getUserPassword(),
                user.getUserRole(), user.getGender(), user.getBirthday(),
                user.getPhone(), user.getAddress(), user.getCreationDate(), user.getCreatedBy()};
        try {
            c = BaseDao.getC();
            c.setAutoCommit(false);
            c.commit();
             execute = BaseDao.execute(c, sql, params);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            try {
                c.rollback();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }finally {
            BaseDao.closeResource(null,null,c);
        }
        if (execute == 1) {
            flag = true;
        }

        return flag;
    };
}
